<?php
class Empresarial_dependiente
{		
	
	public function __construct ()
	{
		
		
	}
	
	/**
	*	Funcion responsable de agregar el formulario de Client Information Form
	*	@autor: JJR
	*	@date: 01/26/2012
	*	@return:
	*/
	
	function addEmpresarialDependiente($data){
		$sql = "INSERT INTO empresa_dependiente (
		emde_code ,
		emde_receipt ,
		emde_date_periodo ,
		emde_periodo ,
		emde_company_name ,
		emde_company_nit ,
		emde_company_address ,
		hdvc_code ,
		emde_value_admission ,
		emde_name_eps ,
		emde_value_eps ,
		emde_name_arp ,
		emde_value_arp ,
		emde_name_afp ,
		emde_value_afp ,
		emde_name_sena ,
		emde_value_sena ,
		emde_name_icbf ,
		emde_value_icbf ,
		emde_name_caja ,
		emde_value_caja ,
		emde_reingreso ,
		emde_aporte_mutual ,
		emde_admin ,
		emde_admin_iva ,
		emde_otros ,
		emde_funeral ,
		emde_ajuste ,
		emde_interes_mora ,
		emde_descuentos ,
		emde_total ,
		emde_letras ,
		emde_recibido ,
		emde_date_create ,
		emde_status)
		VALUES (
		NULL ,
		'".$data->emde_receipt."',
		'".$data->emde_date_periodo."',
		'".date("m-Y")."',
		'".$data->emde_company_name."',
		'".$data->emde_company_nit."',
		'".$data->emde_company_address."',
		'".$data->hdvc_code."',
		'".$data->emde_value_admission."',
		'".$data->emde_name_eps."',
		'".$data->emde_value_eps."',
		'".$data->emde_name_arp."',
		'".$data->emde_value_arp."',
		'".$data->emde_name_afp."',
		'".$data->emde_value_afp."',
		'".$data->emde_name_sena."',
		'".$data->emde_value_sena."',
		'".$data->emde_name_icbf."',
		'".$data->emde_value_icbf."',
		'".$data->emde_name_caja."',
		'".$data->emde_value_caja."',
		'".$data->emde_reingreso."',
		'".$data->emde_aporte_mutual."',
		'".$data->emde_admin."',
		'".$data->emde_admin_iva."',
		'".$data->emde_otros."',
		'".$data->emde_funeral."',
		'".$data->emde_ajuste."',
		'".$data->emde_interes_mora."',
		'".$data->emde_descuentos."',
		'".$data->emde_total."',
		'".$data->emde_letras."',
		'".$data->emde_recibido."',
		'".date("Y-m-d H:i:s")."',
		'Active');";	
		
		if(mysql_query($sql)){		
			return mysql_insert_id ();
		}else
			return false;
	}
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/27/2012
	*	Funcion encargada de obtener los datos de un cliente.
	*	@return:
	*/	
	
	function getEmpresarialDependienteData($id)
	{
		 $sql = "SELECT *
		FROM empresa_dependiente  WHERE emde_status<> 'Deleted' AND emde_code = ".$id;
		$consult = mysql_query($sql);
		$row = mysql_fetch_array($consult);
		
		$data->emde_code 				= $row['emde_code'] ;
		$data->emde_receipt 			= $row['emde_receipt'] ;
		$data->emde_date_periodo 		= $row['emde_date_periodo'] ;
		$data->emde_periodo 			= $row['emde_periodo'] ;
		$data->emde_company_name 		= $row['emde_company_name'] ;
		$data->emde_company_nit 		= $row['emde_company_nit'] ;
		$data->emde_company_address 	= $row['emde_company_address'] ;
		$data->hdvc_code 				= $row['hdvc_code'] ;
		$data->emde_value_admission 	= $row['emde_value_admission'] ;
		$data->emde_name_eps 			= $row['emde_name_eps'] ;
		$data->emde_value_eps 			= $row['emde_value_eps'] ;
		$data->emde_name_arp 			= $row['emde_name_arp'] ;
		$data->emde_value_arp 			= $row['emde_value_arp'] ;
		$data->emde_name_afp 			= $row['emde_name_afp'] ;
		$data->emde_value_afp 			= $row['emde_value_afp'] ;
		$data->emde_name_sena 			= $row['emde_name_sena'] ;
		$data->emde_value_sena 			= $row['emde_value_sena'] ;
		$data->emde_name_icbf 			= $row['emde_name_icbf'] ;
		$data->emde_value_icbf 			= $row['emde_value_icbf'] ;
		$data->emde_name_caja 			= $row['emde_name_caja'] ;
		$data->emde_value_caja	 		= $row['emde_value_caja'] ;
		$data->emde_reingreso 			= $row['emde_reingreso'] ;
		$data->emde_aporte_mutual 		= $row['emde_aporte_mutual'] ;
		$data->emde_admin 				= $row['emde_admin'] ;
		$data->emde_admin_iva 			= $row['emde_admin_iva'] ;
		$data->emde_otros 				= $row['emde_otros'] ;
		$data->emde_funeral 			= $row['emde_funeral'] ;
		$data->emde_ajuste 				= $row['emde_ajuste'] ;
		$data->emde_interes_mora 		= $row['emde_interes_mora'] ;
		$data->emde_descuentos 			= $row['emde_descuentos'] ;
		$data->emde_total 				= $row['emde_total'] ;
		$data->emde_letras 				= $row['emde_letras'] ;
		$data->emde_recibido 			= $row['emde_recibido'] ;
	
		return $data;	
	}	
	
	/**
	*	Esta funcion es responsable de insertar datos en la tabla case que son los campos del tipo de negocio
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@parameters: 
	*	@return:
	*/
	function addBusiness($data){
		$sql = "INSERT INTO my_case (
		case_code,
		case_number,
		pro_comment,
		pro_date_create,
		cli_code,
		bus_code,
		use_code,
		pro_status)
		VALUES (
		NULL,
		'".$data->case_number."',
		'".$data->pro_comment."',
		'".date("Y-m-d H:i:s")."',
		'".$data->cli_code."',
		'".$data->bus_code."',
		'".$data->use_code."'
		'Active');"; 
		
		return mysql_query ($sql);
	}
	
	/**
	*	Esta funcion es la encargada de listar los clientes que existen en el sitio.
	*	@autor: JJR
	*	@date: 01/25/2012
	*	@parameters: 
	*	@return:
	*/
	function empresarialDependienteList($search='',$order_by='', $inicio = 0,$cantidad = 0){
		$where = '';		
		if($search != '')
		$where = " WHERE (hdvc_name like '%".$search."%'
				   OR emde_receipt like '%".$search."%'
				   OR hdvc_nit like '%".$search."%'
				   OR emde_date_periodo like '%".$search."%' 
				   OR emde_periodo like '%".$search."%'		           
				   OR emde_total like '%".$search."%')";
		
		if ($order_by == '')
			$order_by = ' ORDER BY emde_code DESC';
		

		$sql = "SELECT t5.emde_code, t5.emde_receipt, t5.hdvc_code, t5.emde_date_periodo,t5.emde_periodo, t5.emde_total, hc.hdvc_nit, hc.hdvc_name FROM
		(SELECT c.emde_code, c.emde_receipt, c.hdvc_code,  c.emde_date_periodo,c.emde_periodo, c.emde_total	FROM empresa_dependiente c WHERE c.emde_status <> 'Deleted') t5 LEFT JOIN hdv_company hc ON hc.hdvc_code=t5.hdvc_code" .$where."".$order_by;
		if ( !($inicio == 0 && $cantidad == 0 ) )
			$sql .= ' LIMIT '.$inicio.', '.$cantidad.';';
		
		$consult  = mysql_query($sql);
		$data;
		$i = 0;
		while($row = mysql_fetch_array($consult)){
			
			$data[$i]->emde_code= $row['emde_code'];
			$data[$i]->emde_receipt= $row['emde_receipt'];
			$data[$i]->hdvc_code= $row['hdvc_code'];
			$data[$i]->hdvc_name= $row['hdvc_name'];
			$data[$i]->hdvc_nit= $row['hdvc_nit'];
			$data[$i]->emde_date_periodo = $row['emde_date_periodo'];
			$data[$i]->emde_periodo = $row['emde_periodo'];
			$data[$i]->emde_total= $row['emde_total'];	

			$i++;				
		}
		return $data;
	}	

	
	/**
	*	Esta funcion es la encargada de listar los business de los clientes que existen en el sitio.
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@parameters: 
	*	@return:
	*/
	function businessList($search='',$order_by='', $inicio = 0,$cantidad = 0){
		$where = '';		
		if($search != '')
		$where = "  WHERE (	           
				    cli_name like '%".$search."%'
				   OR cli_lastname like '%".$search."%'
				   OR cli_ss like '%".$search."%'
				   OR use_name like '%".$search."%'
				   OR use_lastname like '%".$search."%'
				   OR pro_comment like '%".$search."%'
				   OR pro_date_create like '%".$search."%')";
		
		if ($order_by == '')
			$order_by = ' ORDER BY pro_date_create DESC ';
		

			$sql = "SELECT t6.*, u.use_name, u.use_lastname FROM
			(SELECT t5.*, p.bus_name FROM
			(SELECT t4.*, c.cli_name, c.cli_lastname,c.cli_ss FROM
			(SELECT case_code,case_number,pro_comment,pro_date_create,cli_code,bus_code,use_code FROM my_case WHERE pro_status<>'Deleted') 
			t4 LEFT JOIN client c ON t4.cli_code=c.cli_code)
			t5 LEFT JOIN business_type p ON t5.bus_code=p.bus_code)
			t6 LEFT JOIN user u ON t6.use_code=u.use_code ".$where." ".$order_by.""; 
			
			
		
		if ( !($inicio == 0 && $cantidad == 0 ) )
			$sql .= ' LIMIT '.$inicio.', '.$cantidad.';';
		
		$consult  = mysql_query($sql);
		$data;
		$i = 0;
		while($row = mysql_fetch_array($consult)){
			$data[$i]->case_code= $row['case_code'];
			$data[$i]->cli_code= $row['cli_code'];
			$data[$i]->bus_code = $row['bus_code'];
			$data[$i]->bus_name = $row['bus_name'];
			$data[$i]->pro_comment= $row['pro_comment'];
			$data[$i]->pro_date_create= $row['pro_date_create'];
			$data[$i]->use_code= $row['use_code'];
			$data[$i]->cli_name= $row['cli_name'];
			$data[$i]->cli_lastname= $row['cli_lastname'];
			$data[$i]->cli_ss= $row['cli_ss'];
			$data[$i]->use_name= $row['use_name'];
			$data[$i]->use_lastname= $row['use_lastname'];

			$i++;				
		}
		return $data;
	}	
	
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/28/2012
	*	Funcion encargada de contar la cantidad de business.
	*	@return:
	*/

	function countBusiness(){		
			
		$sql = "SELECT COUNT(*) as cant FROM my_case ";
	
		$consulta = mysql_query($sql);
		$row = mysql_fetch_array($consulta);
			
		return $row['cant'];
	}	
			
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/26/2012
	*	Funcion encargada de contar la cantidad de clientes en la aplicacion.
	*	@return:
	*/
	function countEmpresarialDependiente($search){
		$where = '';		
		if($search != '')
		$where = " WHERE (hdvc_name like '%".$search."%'
				   OR emde_receipt like '%".$search."%'
				   OR hdvc_nit like '%".$search."%'
				   OR emde_date_periodo like '%".$search."%' 
				   OR emde_periodo like '%".$search."%'		           
				   OR emde_total like '%".$search."%')";
		
		$sql = "SELECT COUNT(*) AS cant FROM (SELECT c.emde_code, c.emde_receipt, c.hdvc_code,  c.emde_date_periodo,c.emde_periodo, c.emde_total	FROM empresa_dependiente c WHERE c.emde_status <> 'Deleted') t5 LEFT JOIN hdv_company hc ON hc.hdvc_code=t5.hdvc_code" .$where."";

		$consulta = mysql_query($sql);
		$row = mysql_fetch_array($consulta);
			
		return $row['cant'];
	}	
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/26/2012
	*	Funcion encargada de listar los estados.
	*	@return:
	*/
	function stateList()
	{
		$sql = "SELECT state_code, state_name FROM state";
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->state_code 		= $row["state_code"];
			$data[$i]->state_name 		= $row["state_name"];	
						
			$i++;
		}			
		return $data;
	}	
	
		function epsList()
	{
		$sql = "SELECT eps_code, eps_name FROM eps";
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->eps_code 		= $row["eps_code"];
			$data[$i]->eps_name 		= $row["eps_name"];	
						
			$i++;
		}			
		return $data;
	}	
	
		function arpList()
	{
		$sql = "SELECT arp_code, arp_name FROM arp";
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->arp_code 		= $row["arp_code"];
			$data[$i]->arp_name 		= $row["arp_name"];	
						
			$i++;
		}			
		return $data;
	}
	
		function afpList()
	{
		$sql = "SELECT afp_code, afp_name FROM afp";
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->afp_code 		= $row["afp_code"];
			$data[$i]->afp_name 		= $row["afp_name"];	
						
			$i++;
		}			
		return $data;
	}
	
			function parafiscalesList()
	{
		$sql = "SELECT para_code, para_name, para_porc, para_city FROM parafiscales ORDER BY para_name ASC";

		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->para_code 		= $row["para_code"];
			$data[$i]->para_name 		= $row["para_name"];
			$data[$i]->para_porc 		= $row["para_porc"];
			$data[$i]->para_city 		= $row["para_city"];	
						
			$i++;
		}			
		return $data;
	}
	
		function parameterList()
	{
		
		$sql = "SELECT param_code, param_salary , param_aporte ,param_iva ,param_transporte , param_contribuciones FROM parameters WHERE param_code=1";

		$consult = mysql_query($sql);
		$row = mysql_fetch_array($consult);
			$data->param_code 		= $row["param_code"];
			$data->param_salary 		= $row["param_salary"];
			$data->param_aporte 		= $row["param_aporte"];
			$data->param_iva 		= $row["param_iva"];
			$data->param_transporte 		= $row["param_transporte"];
			$data->param_contribuciones 		= $row["param_contribuciones"];
			
		
		return $data;
	}	



	/**
	*	
	*	@autor: JJR
	*	@date: 01/26/2012
	*	Funcion encargada de listar los users.
	*	@return:
	*/
	function empleadoList()
	{
		$sql = "SELECT hdv_code, hdv_name, hdv_lastname, hdv_cedula, hdv_address FROM hdv";
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->hdv_code 		= $row["hdv_code"];
			$data[$i]->hdv_name 		= $row["hdv_name"];	
			$data[$i]->hdv_lastname 	= $row["hdv_lastname"];
			$data[$i]->hdv_cedula 	= $row["hdv_cedula"];
			$data[$i]->hdv_address 	= $row["hdv_address"];
									
			$i++;
		}			
		return $data;
	}	

	/**
	*	
	*	@autor: JJR
	*	@date: 01/26/2012
	*	Funcion encargada de listar los users.
	*	@return:
	*/
	function companyList()
	{
		$sql = "SELECT hdvc_code, hdvc_nit, hdvc_name, hdvc_address FROM hdv_company";
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->hdvc_code 		= $row["hdvc_code"];
			$data[$i]->hdvc_nit 		= $row["hdvc_nit"];	
			$data[$i]->hdvc_name 	= $row["hdvc_name"];
			$data[$i]->hdvc_address 	= $row["hdvc_address"];
						
			$i++;
		}			
		return $data;
	}
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/26/2012
	*	Funcion encargada de listar los Business Types.
	*	@return:
	*/
	function businessTypeList()
	{
		$sql = "SELECT bus_code, bus_name FROM business_type"; 
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->bus_code 		= $row["bus_code"];
			$data[$i]->bus_name 		= $row["bus_name"];	

			$i++;
		}			
		return $data;
	}	
	
	/**
	*	Funcion responsable de agregar el nombre del business Type.
	*	@autor: JJR
	*	@date: 02/02/2012
	*	@return:
	*/	
	function addBusinessType($data){
		$sql = "INSERT INTO business_type ( bus_code, bus_name) VALUES (NULL, '".$data->bus_name."');";	

		if(mysql_query($sql)){		
			return mysql_insert_id ();
		}else
			return false;
	}	
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/28/2012
	*	Funcion encargada de listar los case number o Title name del Negocio.
	*	@return:
	*/
	function caseNumberList()
	{
		$sql = "SELECT case_code, case_number FROM my_case"; 
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->case_code 		= $row["case_code"];
			$data[$i]->case_number 		= $row["case_number"];	

			$i++;
		}			
		return $data;
	}		
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/26/2012
	*	Funcion encargada de listar los Business Types.
	*	@return:
	*/
	function clientNameList()
	{
		$sql = "SELECT cli_code, cli_name, cli_lastname FROM client"; 
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->cli_code 		= $row["cli_code"];
			$data[$i]->cli_name 		= $row["cli_name"];	
			$data[$i]->cli_lastname 	= $row["cli_lastname"];	
			$i++;
		}			
		return $data;
	}	

	

	/**
	*	@autor: JJR
	*	@date: 01/28/2012
	*	Funcion encargada de Actualizar los clientes.
	*	@return:
	*/
	function updateEmployed($new_data){	
		$sql="UPDATE hdv SET 
		hdv_name = '".$new_data->hdv_name."',
		hdv_lastname = '".$new_data->hdv_lastname."',
		hdv_cedula = '".$new_data->hdv_cedula."',
		hdv_address = '".$new_data->hdv_address."',
		hdv_city = '".$new_data->hdv_city."',
		state_code = '".$new_data->state_code."',
		hdv_birth = '".$new_data->hdv_birth."',
		hdv_funeral = '".$new_data->hdv_funeral."',
		eps_code = '".$new_data->eps_code."',
		arp_code = '".$new_data->arp_code."',
		afp_code = '".$new_data->afp_code."',
		hdv_email = '".$new_data->hdv_email."',
		ase_code = '".$new_data->ase_code."'
		WHERE hdv_code =".$new_data->hdv_code.";"; 
		
	
		if(mysql_query($sql)){		
			return true;
		}else
			return false;
	}			
	
	/**
	*	Funcion encargada de Actualizar los datos de un cliente.
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@parameters: 
	*	@return:
	*/	
		function deleteClientePila($clip_code)
	{
		$sql="UPDATE `cliente_pila` SET `clip_status` =  'Deleted' WHERE `clip_code` =".$clip_code;
		return mysql_query($sql);
	}

	/**
	*	
	*	@autor: JJR
	*	@date: 01/27/2012
	*	Funcion encargada de obtener los datos de un cliente.
	*	@return:
	*/	
	
	function getBusinessData($case_code)
	{
		 $sql = "SELECT p.case_code, p.case_number, p.pro_comment, p.cli_code, p.bus_code, p.use_code
		FROM my_case p WHERE p.case_code = ".$case_code;
		$consult = mysql_query($sql);
		$row = mysql_fetch_array($consult);
		
		$data->case_code   				= $row['case_code'] ;
		$data->case_number   				= $row['case_number'] ;
		$data->pro_comment   			= $row['pro_comment'] ;
		$data->cli_code   				= $row['cli_code'] ;
		$data->bus_code  				= $row['bus_code'] ;
		$data->use_code 				= $row['use_code'] ;
		
		
		return $data;	
	}	
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/30/2012
	*	Funcion encargada de Actualizar los Business.
	*	@return:
	*/	
	
		function updateBusiness($new_data){
	
		$sql="UPDATE case SET 
		case_number     = '".$new_data->case_number."',
		pro_comment    = '".$new_data->pro_comment."',
		cli_code    = '".$new_data->cli_code."',
		bus_code    = '".$new_data->bus_code."',
		use_code    = '".$new_data->use_code."'
		WHERE case_code =".$new_data->case_code.";";  
			
		if(mysql_query($sql)){		
			return mysql_insert_id ();
		}else
			return false;
	}	

	/**
	*	Funcion respponsable de agregar un telefono para un cliente;
	*	@autor: JSL
	*	@date: 29/01/2012
	*/
	function addTelephone($data){
		$sql = "INSERT INTO telephone (tel_code, tel_number, tel_ext, tel_type, id_fk) VALUES (NULL, '".$data->tel_number."', '".$data->tel_ext."', 'Employed', '".$data->id_fk."');";
		
		if( mysql_query ($sql) ) 
			return true;	
		else
			return false;
	}
	
	/**
	*	Funcion responsable de listar el/los telefonos de un clientes.
	*	@autor: JSL
	*	@date: 30/01/2012
	*/
	function getTelephones($id_fk){
		$sql = "SELECT t.tel_code, t.tel_number, t.tel_ext FROM telephone t WHERE t.tel_type='Employed' AND t.id_fk=".$id_fk." ORDER BY tel_code;";
		$data = array ();
		if ( $result = mysql_query ($sql) ){
			$i = 0;
			while ( $row = mysql_fetch_array($result) ){
				$data[$i]->tel_code = $row["tel_code"];
				$data[$i]->tel_number = $row["tel_number"];
				$data[$i]->tel_ext = $row["tel_ext"];
				
				$i++;
			}
		}
				
		return $data;
	}
	
	/**
	*	Funcion responsable eliminar los telefonos de un cliente.
	*	@autor: JSL
	*	@date: 30/01/2012
	*/
	function deleteTelephones($id_fk){
		$sql = "DELETE FROM telephone  WHERE tel_type='Employed' AND id_fk=".$id_fk.";";
		return mysql_query ($sql);
	}
	
	/**
	*	Funcion encargada de Actualizar el status de un business a deleted
	*	@autor: JJR
	*	@date: 01/30/2012
	*	@parameters: 
	*	@return:
	*/	
		function deleteBusiness($case_code)
	{
		$sql="UPDATE my_case SET `case_status` =  'Deleted' WHERE `case_code` =".$case_code;
		return mysql_query($sql);
	}
	
	/**
	*	Esta funcion es la encargada de listar los clientes por advisers.
	*	@autor: JJR
	*	@date: 02/09/2012
	*	@parameters: 
	*	@return:
	*/
	function reportClientList($use_code){

		$sql = " SELECT t2.case_code,t2.cli_code,t2.use_code,t2.use_name,t2.use_lastname,t2.cli_name, t2.cli_lastname,t2.cli_address,t2.cli_city,t2.state_code,t2.cli_zip_code,t2.cli_status,st.state_name FROM
		(SELECT t1.case_code,t1.cli_code,t1.use_code,t1.use_name,t1.use_lastname,cl.cli_name, cl.cli_lastname,cl.cli_address,cl.cli_city,cl.state_code,cl.cli_zip_code,cl.cli_status FROM
		(SELECT mc.case_code, mc.cli_code, mc.use_code, u.use_name, u.use_lastname FROM my_case mc LEFT JOIN user u ON u.use_code=mc.use_code)t1 LEFT JOIN client cl ON cl.cli_code = t1.cli_code WHERE cl.cli_status = 'Active')t2 LEFT JOIN state st ON st.state_code=t2.state_code WHERE use_code=".$use_code;
		
		$consult  = mysql_query($sql);
		$data;
		$i = 0;
		while($row = mysql_fetch_array($consult)){
			$data[$i]->cli_code= $row['cli_code'];
			$data[$i]->use_code= $row['use_code'];
			$data[$i]->use_name = $row['use_name'];
			$data[$i]->use_lastname= $row['use_lastname'];
			$data[$i]->cli_name= $row['cli_name'];
			$data[$i]->cli_lastname= $row['cli_lastname'];
			$data[$i]->cli_address= $row['cli_address'];	
			$data[$i]->cli_city= $row['cli_city'];
			$data[$i]->state_code= $row['state_code'];
			$data[$i]->cli_zip_code= $row['cli_zip_code'];
			$data[$i]->state_name= $row['state_name'];
			$data[$i]->telephones = $this->getTelephones((int) $row['cli_code']);

			$i++;				
		}
		return $data;
	}		
	
	/**
	*	Esta funcion es la encargada de listar los loan Numbers de los clientes.
	*	@autor: JJR
	*	@date: 02/13/2012
	*	@parameters: 
	*	@return:
	*/
	function loanNumberList(){

		$sql = " SELECT t0.case_code, t0.loan_number, t0.cli_code, cl.cli_name, cl.cli_lastname FROM
		(SELECT re.case_code,re.loan_number,mc.cli_code FROM request re JOIN my_case mc WHERE re.case_code=mc.case_code)t0 LEFT JOIN client cl ON cl.cli_code=t0.cli_code";
		
		
		$consult  = mysql_query($sql);
		$data;
		$i = 0;
		while($row = mysql_fetch_array($consult)){
			$data[$i]->cli_code= $row['cli_code'];
			$data[$i]->use_code= $row['use_code'];
			$data[$i]->use_name = $row['use_name'];
			$data[$i]->use_lastname= $row['use_lastname'];
			$data[$i]->cli_name= $row['cli_name'];
			$data[$i]->cli_lastname= $row['cli_lastname'];
			$data[$i]->cli_address= $row['cli_address'];	
			$data[$i]->cli_city= $row['cli_city'];
			$data[$i]->state_code= $row['state_code'];
			$data[$i]->cli_zip_code= $row['cli_zip_code'];
			$data[$i]->state_name= $row['state_name'];
			$data[$i]->telephones = $this->getTelephones((int) $row['cli_code']);

			$i++;				
		}
		return $data;
	}		
	
	/////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos los paises.
	/////////////////////////////////////////////////////////////////////////////
	function listEmployedSelect()
	{
		$sql = "SELECT t4.hdv_code, e.hdv_name, e.hdv_lastname FROM
		(SELECT c.hdv_code FROM contract c WHERE c.cont_status<>'Deleted') t4 LEFT JOIN hdv e ON t4.hdv_code=e.hdv_code ORDER BY e.hdv_name ASC";
		$consult  = mysql_query($sql);
		$array = '';
//		$array .= '<option value="" disabled selected="selected">Seleccione..</option>';	
		while($row = mysql_fetch_array($consult))
		{
			if($code == $row['hdv_code'])
				$array .= '<option value="'.$row['hdv_code'].'" selected="selected">'.$row['hdv_name'].' '.$row['hdv_lastname'].'</option>';		
			else
				$array .= '<option value="'.$row['hdv_code'].'">'.$row['hdv_name']  .' '.$row['hdv_lastname'].'</option>';		
		}
		return $array;	
	}	
	
	/////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos los paises.
	/////////////////////////////////////////////////////////////////////////////
	function listEmployedCedSelect($code = '')
	{
		$sql = "SELECT t4.hdv_code, e.hdv_cedula FROM
		(SELECT c.hdv_code FROM contract c WHERE c.cont_status<>'Deleted') t4 LEFT JOIN hdv e ON t4.hdv_code=e.hdv_code ORDER BY e.hdv_cedula ASC";
		$consult  = mysql_query($sql);
		$array = '';
//		$array .= '<option value="" disabled selected="selected">Seleccione..</option>';	
		while($row = mysql_fetch_array($consult))
		{
			if($code == $row['hdv_code'])
				$array .= '<option value="'.$row['hdv_code'].'" selected="selected">'.$row['hdv_cedula'].'</option>';		
			else
				$array .= '<option value="'.$row['hdv_code'].'">'.$row['hdv_cedula'].'</option>';		
		}
		return $array;	
	}	
	
    /////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos las zonas de una ciudad.
	/////////////////////////////////////////////////////////////////////////////	
function listEpsSelect($code)
	{
		$sql = "SELECT t4.hdvc_code, t4.eps_code, e.eps_name, e.eps_porc FROM
		(SELECT c.hdvc_code, c.eps_code FROM contract_company c WHERE c.hdvc_code =".$code.") t4 LEFT JOIN eps e ON t4.eps_code=e.eps_code";
		
		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		$array[0] = ucfirst(strtolower($row['eps_name']));
		$array[1] = $row['eps_porc'];
		return $array;	
	}		

    /////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos las zonas de una ciudad.
	/////////////////////////////////////////////////////////////////////////////	
function listArpSelect($code)
	{
		$sql = "SELECT t4.hdvc_code, t4.arp_code, e.arp_name, e.arp_porc FROM
		(SELECT c.hdvc_code, c.arp_code FROM contract_company c WHERE c.hdvc_code =".$code.") t4 LEFT JOIN arp e ON t4.arp_code=e.arp_code";
		
		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		$array[0] = ucfirst(strtolower($row['arp_name']));
		$array[1] = $row['arp_porc'];				
		return $array;	
	}	

    /////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos las zonas de una ciudad.
	/////////////////////////////////////////////////////////////////////////////	
function listAfpSelect($code)
	{
		$sql = "SELECT t4.hdvc_code, t4.afp_code, e.afp_name, e.afp_porc FROM
		(SELECT c.hdvc_code, c.afp_code FROM contract_company c WHERE c.hdvc_code =".$code.") t4 LEFT JOIN afp e ON t4.afp_code=e.afp_code";

		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		$array[0] = ucfirst(strtolower($row['afp_name']));
		$array[1] = $row['afp_porc'];				
		return $array;
	}
	
    /////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos las zonas de una ciudad.
	/////////////////////////////////////////////////////////////////////////////	
function listParafiscalSelect($code = '')
	{
		$sql = "SELECT t4.hdv_code, t4.para_code, e.para_name, e.para_porc FROM
		(SELECT c.hdv_code, c.para_code FROM contract c WHERE c.hdv_code =".$code.") t4 LEFT JOIN parafiscales e ON t4.para_code=e.para_code";

		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		$array[0] = ucfirst(strtolower($row['para_name']));
		$array[1] = $row['para_porc'];				
		return $array;
	}	
	
    /////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos las zonas de una ciudad.
	/////////////////////////////////////////////////////////////////////////////	
function listParameterSelect()
	{
		$sql = "SELECT param_code, param_iva  FROM parameters WHERE param_code=1";

		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		
		return ucfirst(strtolower($row['param_iva']));	
	}




    /////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos las zonas de una ciudad.
	/////////////////////////////////////////////////////////////////////////////	
function listAportesSelect($code = '')
	{
		$sql = "
		SELECT c.hdvc_code, c.coco_admin, c.coco_aporte_mutual FROM contract_company c WHERE c.hdvc_code =".$code;
		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		$array[0] = ucfirst(strtolower($row['hdvc_code']));
		$array[1] = $row['coco_admin'];
		$array[2] = $row['coco_aporte_mutual'];
			
		return $array;
	}		

    /////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos las zonas de una ciudad.
	/////////////////////////////////////////////////////////////////////////////	
function listValueSelect($code = '')
	{
		$sql = "
		SELECT c.hdvc_code, c.coco_pay_value FROM contract_company c WHERE c.hdvc_code =".$code;
		
		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		
		return ucfirst(strtolower($row['coco_pay_value']));	
	}	
	
    /////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos las zonas de una ciudad.
	/////////////////////////////////////////////////////////////////////////////	
function listCompanySelect($code = '')
	{
		$sql = "SELECT t4.hdv_code, t4.hdvc_code, e.hdvc_name, e.hdvc_address, e.hdvc_nit FROM
		(SELECT c.hdv_code, c.hdvc_code FROM contract c WHERE c.hdv_code =".$code.") t4 LEFT JOIN hdv_company e ON t4.hdvc_code=e.hdvc_code";

		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		$array[0] = $row['hdvc_code'];
		$array[1] = ucfirst(strtolower($row['hdvc_name']));
		$array[2] = ucfirst(strtolower($row['hdvc_nit']));
		$array[3] = $row['hdvc_address'];				
		return $array;
	}
	
	function getNumberReceipt(){
	  $sql = "SELECT max(emde_code) as jjr FROM empresa_dependiente ";
	 
		  if ( $result = mysql_query ($sql) ){
				if ( $row = mysql_fetch_array ($result) ){
					return (int) $row["jjr"];
				}
				else
					return false;
			}
			else
				return false;
		}		
		
    /////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos las zonas de una ciudad.
	/////////////////////////////////////////////////////////////////////////////	
function getCompanySelect($code)
	{
		$sql = "SELECT c.hdvc_code, c.hdvc_name,c.hdvc_nit, c.hdvc_address FROM hdv_company c WHERE c.hdvc_code =".$code;
		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		$array[0] = $row['hdvc_name'];
		$array[1] = $row['hdvc_nit'];
		$array[2] = $row['hdvc_address'];
		$array[3] = $row['hdvc_code'];				
		return $array;
	}	
	
	

	function getSalTotalData($code)
	{
		 $sql = "SELECT sum(cont_sal_value) AS total from contract where hdvc_code=".$code;
		$consult = mysql_query($sql);
		$array ='';
		$row = mysql_fetch_array($consult);
		
		$array[0] = $row['total'];
		
		return $array;	
	}	
	
	function getFuneralTotalData($code)
	{
		 $sql = "SELECT sum(cont_funeral) AS totalFuneral from contract where hdvc_code=".$code;
		$consult = mysql_query($sql);
		$array ='';
		$row = mysql_fetch_array($consult);
		
		$array[0] = $row['totalFuneral'];
		
		return $array;	
	}	

function listSenaSelect()
	{
		$sql = "SELECT c.paco_code, c.paco_name, c.paco_porc FROM parafiscales_company c WHERE c.paco_code = 1";
		
		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		$array[0] = ucfirst(strtolower($row['paco_name']));
		$array[1] = $row['paco_porc'];				
		return $array;	
	}
	
function listIcbfSelect()
	{
		$sql = "SELECT c.paco_code, c.paco_name, c.paco_porc FROM parafiscales_company c WHERE c.paco_code = 2";
		
		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		$array[0] = ucfirst(strtolower($row['paco_name']));
		$array[1] = $row['paco_porc'];				
		return $array;	
	}	
	
function listCajaSelect()
	{
		$sql = "SELECT c.paco_code, c.paco_name, c.paco_porc FROM parafiscales_company c WHERE c.paco_code = 3";
		
		$consult  = mysql_query($sql);
		$array = '';
		$row = mysql_fetch_array($consult);
		$array[0] = ucfirst(strtolower($row['paco_name']));
		$array[1] = $row['paco_porc'];				
		return $array;	
	}		

}
?>